package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import contacts614.po.Room;
import contacts614.po.Student;
import dao.IStudentDao;
import db.DbConnection;

public class StudentImpl implements IStudentDao {// 数据库
	/**
	 * 数据库添加学生信息
	 */
	public void addStudent(Student student) {//
		// Swing String,String向int,date转
		Connection conn = DbConnection.getConnection();
		String studentNo = student.getStudentNo();
		String studentName = student.getStudentName();
		Room room = student.getRoom();// 类型不对，要String类型
		String roomNo = room.getRoomNo();
		Date studentBirth = student.getStudentBirth();
		String studentTelephone = student.getStudentTelephone();
		String studentEmail = student.getStudentEmail();
		String studentAddress = student.getStudentAddress();
		String sql = "insert into student values(?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, studentNo);
			ps.setString(2, studentName);
			ps.setString(3, roomNo);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String stringDate = sdf.format(studentBirth);
			ps.setDate(4, java.sql.Date.valueOf(stringDate));// util.Date转String，再转sql.Date
			ps.setString(5, studentTelephone);
			ps.setString(6, studentEmail);
			ps.setString(7, studentAddress);
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(ps, null);
		}
	}

	/**
	 * 数据库修改学生信息
	 */
	public void updateStudent(Student student) {
		Connection conn = DbConnection.getConnection();
		String studentNo = student.getStudentNo();
		String studentName = student.getStudentName();
		Room room = student.getRoom();
		String roomNo = room.getRoomNo();
		Date studentBirth = student.getStudentBirth();
		String studentTelephone = student.getStudentTelephone();
		String studentEmail = student.getStudentEmail();
		String studentAddress = student.getStudentAddress();
		String sql = "UPDATE student SET student_name=?,room_no=?,student_birth=?,student_tel=?,"
				+ "student_email=?,student_address=? WHERE student_no=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, studentName);

			ps.setString(2, roomNo);

			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String stringDate = sdf.format(studentBirth);
			ps.setDate(3, java.sql.Date.valueOf(stringDate));
			ps.setString(4, studentTelephone);
			ps.setString(5, studentEmail);
			ps.setString(6, studentAddress);
			System.out.println(studentAddress);
			ps.setString(7, studentNo);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(ps, null);
		}
	}

	/**
	 * 数据库查找学生信息
	 */
	public Student findOneStudent(String studentNo) {
		Connection conn = DbConnection.getConnection();
		Student student = new Student();

//		List<Student> studentList = new ArrayList<Student>();
		String sql = "select student_no,student_name,room.room_no,student_birth,student_tel,student_email,student_address from student,room where student.room_no=room.room_no and student_no =?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, studentNo);
			rs = ps.executeQuery();
			while (rs.next()) {
				// student=new Student();

				String studentNo1 = rs.getString(1);

				String studentName = rs.getString(2);
				String roomNo = rs.getString(3);
				java.util.Date utilDate = new java.util.Date(rs.getDate(4).getTime());
				System.out.println(roomNo);
				String studentTelephone = rs.getString(5);
				String studentEmail = rs.getString(6);
				String studentAddress = rs.getString(7);
				Room room = new Room();
				room.setRoomNo(roomNo);
				student.setStudentNo(studentNo1);
				student.setStudentName(studentName);
				student.setRoom(room);
				student.setStudentBirth(utilDate);
				student.setStudentTelephone(studentTelephone);
				student.setStudentEmail(studentEmail);
				student.setStudentAddress(studentAddress);

//				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(ps, rs);
		}
		return student;
	}

	/**
	 * 数据库展示学生信息
	 */
	public List<Student> findAllStudent() {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "select student_no,student_name,room_no,student_birth,student_tel,student_email,student_address from student";
		// sql多表查询语句不要查*号，不然不知道查出的顺序,而且roomId不知道属于哪个表
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String studentNo = rs.getString(1);
				String studentName = rs.getString(2);
				String roomNo = rs.getString(3);
				Date studentBirth = rs.getDate(4);
				String studentTelephone = rs.getString(5);
				String studentEmail = rs.getString(6);
				String studentAddress = rs.getString(7);// 注意顺序
				Room room = new Room();// 关键
				room.setRoomNo(roomNo);
				Student student = new Student(studentNo, studentName, room, studentBirth, studentTelephone,
						studentEmail, studentAddress);// 关键,要往student里传值，不然查到了也看不到,这里的room是上一行的
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(ps, rs);
		}
		return studentList;
		// student.getRoom().getRoomName();
	}

	/**
	 * 数据库删除学生信息
	 */
	public void deleteStudent(String studentNo) {
		Connection conn = DbConnection.getConnection();
		String sql = "delete from student where student_no=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, studentNo);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(ps, null);
		}
	}
}
